USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_avg_val') BEGIN
	DROP  FUNCTION  tsql.fn_avg_val
	PRINT '<<< DROP FUNCTION tsql.fn_avg_val - Completed with SUCCESS >>>'
END
GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.fn_avg_val
Type:			User defined function 
Owner:			
Description:	Returns avg value from a list of values 
Parameters:		@list = list of values comma delimited
				@compare_as = indicates if the list of values is a list of numbers or text
Dependens on:  	fn_split
Usage:			SELECT [tsql].[fn_avg_val] ('1,2,3,4,5','number')
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_avg_val&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		12/02/2009 - Adrian E Dudau - Function created
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE FUNCTION [tsql].[fn_avg_val] (@List NVARCHAR(MAX),@compare_as NVARCHAR(50))
RETURNS NVARCHAR(MAX)
AS

BEGIN
	DECLARE @Result AS NVARCHAR(MAX)	

	IF ISNULL(@compare_as,'') IN ('NO','N','NUMBER') BEGIN 
		SET @compare_as = 'NUMBER'
	END	
	ELSE BEGIN
		SET @compare_as = 'STRING'
	END

	-- If the List is empty or null return input string
	SET @Result = @list

	-- Else get min
	IF ISNULL(@list,'') <> '' BEGIN
		SELECT @Result = AVG(CASE WHEN @compare_as = 'NUMBER' AND ISNUMERIC(LTRIM(RTRIM(ISNULL(item_value,'0')))) = 1 THEN 			
									 CAST(item_value AS FLOAT)
								  ELSE item_value	 
							 END)
		FROM tsql.fn_split(@list,',')
	END

	RETURN @Result 
END

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_avg_val') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_avg_val - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_avg_val - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------
